package dao;

import entity.Config;
import util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ConfigDAO {
    public void add(Config config) {
        String sql = "insert into config values(null,?,?)";
        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);){

            ps.setString(1,config.key);
            ps.setString(2,config.value);
            ps.execute();

            ResultSet rs = ps.getGeneratedKeys();//给出自增长id，赋给config
            if(rs.next()) {
                int id = rs.getInt(1);
                config.setId(id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(Config config) {
        String sql = "update config set key_=?,value=? where id=?";
        try(Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1,config.key);
            ps.setString(2,config.value);
            ps.setInt(3,config.id);
            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int id) {
        String sql = "delete from config where id = " + id;
        try(Connection c = DBUtil.getConnection();
            Statement s = c.createStatement();) {

            s.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Config get(int id) {
        Config config = null;
        String sql = "select *from config where id = "+id;

        try(Connection c = DBUtil.getConnection();
        Statement s = c.createStatement();) {

            ResultSet rs = s.executeQuery(sql);
            if(rs.next())
            {
                config = new Config();
                String key = rs.getString("key_");
                String value = rs.getString("value");
                config.key = key;
                config.value = value;
                config.id = id;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return config;
    }

    public List<Config> list() {

        return list(0,Short.MAX_VALUE);
    }

    public List<Config> list(int start, int count) {
        List<Config> configList = new ArrayList<Config>();

        String sql = "select * from config order by id desc limit ?,?";

        try(Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1,start);
            ps.setInt(2,count);

            ResultSet rs = ps.executeQuery();

            while (rs.next())
            {
                Config config = new Config();
                config.id = rs.getInt(1);
                config.key = rs.getString("key_");
                config.value = rs.getString("value");
                configList.add(config);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } ;

        return configList;
    }

    public int getTotal() {
        int total = 0;
        String sql = "select count(*) from config";

        try(Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);) {

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {//这里站长用的while
                total = rs.getInt(1);
            }

            System.out.println("total:" + total);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return total;
    }

    public Config getByKey(String key) {
        Config config = null;
        String sql = "select * from config where key_ = ?";

        try (Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1,key);
            ResultSet rs = ps.executeQuery();

            if(rs.next())
            {
                config = new Config();
                config.id = rs.getInt("id");
                config.key = key;
                config.value = rs.getString("value");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return config;
    }
}
